﻿Imports System.Data
Imports System.Data.SqlClient
Imports Control_Vuelos_BE

Public Class ConsolidadoADO
    ReadOnly MiConexion As New ConexionADO()
    ReadOnly MiConexion2 As New ConexionADO()
    ReadOnly cnx As New SqlConnection(MiConexion2.GetCnx)
    ReadOnly cmd As New SqlCommand()

    Public Function ActualizarProvision(ByVal pConsolidado As ConsolidadoBE) As Boolean
        Dim oTran As SqlTransaction
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Try
                cn.Open()
                oTran = cn.BeginTransaction(IsolationLevel.Serializable)
                Dim cmd0 As New SqlCommand("SELECT count(*) FROM dbo.V_Provision WHERE NroTicket = @NroTicket", cn)
                Using cmd0
                    cmd0.CommandType = CommandType.Text
                    cmd0.Parameters.Add("@NroTicket", SqlDbType.Char, 8)
                    cmd0.Parameters("@NroTicket").Value = pConsolidado.NroTicket.Trim
                    cmd0.Transaction = oTran
                    Dim nReg As Integer = CInt(cmd0.ExecuteScalar())
                    ' creo el registro del ticket
                    Dim sInstr As String
                    ' Si existe el ticket
                    If nReg > 0 Then
                        ' actualizo el registro del ticket
                        sInstr = "UPDATE dbo.V_Provision SET NroPeriodo=@NroPeriodo, CodAsiento=@CodAsiento, NroAsiento=@NroAsiento, TipDocumento=@TipDocumento, NroDocumento=@NroDocumento, FchIngreso=@FchIngreso, FchEmision=@FchEmision, FchVuelo=@FchVuelo, MonTipoCambio=@MonTipoCambio, CodMoneda=@CodMoneda, MonTotal=@MonTotal, CodEmpresa=@CodEmpresa, NroConPago=@NroConPago, FlgDetraccion=@FlgDetraccion where NroTicket=@NroTicket"
                    Else
                        sInstr = "INSERT INTO dbo.V_Provision(NroTicket, NroPeriodo, CodAsiento, NroAsiento, TipDocumento, NroDocumento, FchIngreso, FchEmision, FchVuelo, MonTipoCambio, CodMoneda, MonTotal, CodEmpresa, NroConPago, FlgDetraccion) " & "VALUES (@NroTicket, @NroPeriodo, @CodAsiento, @NroAsiento, @TipDocumento, @NroDocumento, @FchIngreso, @FchEmision, @FchVuelo, @MonTipoCambio, @CodMoneda, @MonTotal, @CodEmpresa, @NroConPago, @FlgDetraccion)"
                    End If
                    Dim cmd As New SqlCommand(sInstr, cn)
                    Using cmd
                        cmd.Parameters.AddWithValue("@NroTicket", pConsolidado.NroTicket)
                        cmd.Parameters.AddWithValue("@NroPeriodo", pConsolidado.NroPeriodo)
                        cmd.Parameters.AddWithValue("@CodAsiento", pConsolidado.CodAsiento)
                        cmd.Parameters.AddWithValue("@NroAsiento", pConsolidado.NroAsiento)
                        cmd.Parameters.AddWithValue("@TipDocumento", pConsolidado.TipDocumento)
                        cmd.Parameters.AddWithValue("@NroDocumento", pConsolidado.NroDocumento)
                        cmd.Parameters.AddWithValue("@FchIngreso", pConsolidado.FchIngreso)
                        cmd.Parameters.AddWithValue("@FchEmision", pConsolidado.FchEmision)
                        cmd.Parameters.AddWithValue("@FchVuelo", pConsolidado.FchVuelo)
                        cmd.Parameters.AddWithValue("@MonTipoCambio", pConsolidado.MonTipoCambio)
                        cmd.Parameters.AddWithValue("@CodMoneda", pConsolidado.CodMoneda)
                        cmd.Parameters.AddWithValue("@MonTotal", pConsolidado.MonTotal)
                        cmd.Parameters.AddWithValue("@CodEmpresa", pConsolidado.CodEmpresa)
                        cmd.Parameters.AddWithValue("@NroConPago", pConsolidado.NroConPago)
                        cmd.Parameters.AddWithValue("@FlgDetraccion", pConsolidado.FlgDetraccion)
                        cmd.Transaction = oTran
                        cmd.ExecuteNonQuery()
                    End Using
                    oTran.Commit()
                End Using
                cn.Close()
                Return True
            Catch myException As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (AgregarProvision) --> " & myException.Message, MsgBoxStyle.Critical)
                Return False
            End Try
        End Using
    End Function

    Public Function ActualizarProvisionManifiesto(ByVal sVuelo As String, ByVal sTicket As String) As Boolean
        Const sInstruccion As String = " Update dbo.C_Manifiesto Set NroTicket = @NroTicket, EstadoManifiesto = @Estado WHERE CodViaje = @CodVuelo"
        Using cn As New SqlConnection(MiConexion.GetCnx)
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Try
                Dim mySqlCommand As SqlCommand = New SqlCommand(sInstruccion, cn) With {.Transaction = oTran}
                mySqlCommand.Parameters.AddWithValue("@CodVuelo", sVuelo.Trim)
                mySqlCommand.Parameters.AddWithValue("@NroTicket", IIf(sTicket.Trim Is String.Empty, DBNull.Value, sTicket.Trim))
                mySqlCommand.Parameters.AddWithValue("@Estado", IIf(sTicket Is String.Empty, "2", "3"))
                mySqlCommand.ExecuteNonQuery()
                oTran.Commit()
                cn.Close()
                Return True
            Catch myException As Exception
                oTran.Rollback()
                cn.Close()
                MsgBox("ConsolidadoADO (ActualizarProvisionManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
                Return False
            End Try
        End Using
    End Function

    Public Function ActualizarProvisionManifiestoDetalle(ByVal sVuelo As String, ByVal nReserva As Integer, ByVal sItem As String, ByVal sComunidad As String, nPasaje As Decimal, ByVal lNoEditar As Boolean) As Boolean
        Const sInstruccion As String = " Update dbo.D_Manifiesto Set NroItem = @Item, CodComunidad = @Comunidad, MonPasaje = @Pasaje, FlgNoEditar = @NoEditar WHERE CodViaje = @CodVuelo AND CodReserva = @Reserva"
        Using cn As New SqlConnection(MiConexion.GetCnx)
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Try
                Dim mySqlCommand As SqlCommand = New SqlCommand(sInstruccion, cn) With {.Transaction = oTran}
                mySqlCommand.Parameters.AddWithValue("@CodVuelo", sVuelo.Trim)
                mySqlCommand.Parameters.AddWithValue("@Reserva", nReserva)
                mySqlCommand.Parameters.AddWithValue("@Item", sItem.Trim)
                mySqlCommand.Parameters.AddWithValue("@Comunidad", IIf(sComunidad.Trim Is String.Empty, DBNull.Value, sComunidad.Trim))
                mySqlCommand.Parameters.AddWithValue("@Pasaje", nPasaje)
                mySqlCommand.Parameters.AddWithValue("@NoEditar", lNoEditar)
                mySqlCommand.ExecuteNonQuery()
                oTran.Commit()
                cn.Close()
                Return True
            Catch myException As Exception
                oTran.Rollback()
                cn.Close()
                MsgBox("ConsolidadoADO (ActualizarProvisionManifiestoDetalle) --> " & myException.Message, MsgBoxStyle.Critical)
                Return False
            End Try
        End Using
    End Function

    Public Function ActualizarProvisionManifiestoProducto(ByVal sVuelo As String, ByVal lFlag As Boolean) As Boolean
        Const sInstruccion As String = " Update dbo.C_Manifiesto Set FlgProducto = @lProducto WHERE CodViaje = @CodVuelo"
        Using cn As New SqlConnection(MiConexion.GetCnx)
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Try
                Dim mySqlCommand As SqlCommand = New SqlCommand(sInstruccion, cn) With {.Transaction = oTran}
                mySqlCommand.Parameters.AddWithValue("@CodVuelo", sVuelo.Trim)
                mySqlCommand.Parameters.AddWithValue("@lProducto", lFlag)
                mySqlCommand.ExecuteNonQuery()
                oTran.Commit()
                cn.Close()
                Return True
            Catch myException As Exception
                oTran.Rollback()
                cn.Close()
                MsgBox("ConsolidadoADO (ActualizarProvisionManifiestoProducto) --> " & myException.Message, MsgBoxStyle.Critical)
                Return False
            End Try
        End Using
    End Function

    Public Function CargarCabeceraManifiesto(ByVal sFchVuelo As String) As DataTable
        Dim sdia As String = Strings.Mid(sFchVuelo, 1, 2)
        Dim smes As String = Strings.Mid(sFchVuelo, 4, 2)
        Dim sanio As String = Strings.Mid(sFchVuelo, 7, 4)
        Dim sql As String = "select m.CodViaje as CodVuelo,DesTramo=lo.NomLugar+'-'+ld.NomLugar,m.FchViaje,m.EstadoManifiesto,m.FlgValija,isnull(m.FlgProducto,0) as FlgProducto,CAST(case m.EstadoManifiesto when '3' then 1 else 0 end AS BIT) AS FlgSeleccion from dbo.c_manifiesto m " &
                            "inner join dbo.m_lugar lo on 'L'+m.CodlugarOrigen=lo.TipLugar+lo.CodLugar " &
                            "inner join dbo.m_lugar ld on 'L'+m.CodlugarDestino=lo.TipLugar+ld.CodLugar " &
                            "where (year(m.fchViaje)= " & sanio & " And Month(m.fchViaje) = " & smes & " And Day(m.fchViaje) = " & sdia & ") " &
                            "and m.EstadoManifiesto in ('2','3')"
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Try
                Dim dt As New DataTable
                cn.Open()
                Using da As New SqlDataAdapter(sql, cn)
                    da.Fill(dt)
                End Using
                cn.Close()
                Return dt
            Catch myException As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (CargarCabeceraManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function CargaCorrelativoTicket() As String
        Dim rowsupdate As Integer
        Dim intkey As Integer
        Dim stick As String
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Try
                cn.Open()
                Using xcmd As New SqlCommand("pr_nroticket", cn)
                    With xcmd
                        .CommandType = CommandType.StoredProcedure
                        .Parameters.Add("@onrotic", SqlDbType.Int)
                        .Parameters(0).Direction = ParameterDirection.Output
                        rowsupdate = .ExecuteNonQuery
                        If IsDBNull(.Parameters(0).Value) Then
                            intkey = 1
                        Else
                            intkey = .Parameters(0).Value
                        End If
                        stick = "00000000" & CType(intkey, String)
                    End With
                End Using
                cn.Close()
                Return Strings.Right(stick, 8)
            Catch ex As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (CargaCorrelativoTicket) --> " & ex.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function CargarDetalleManifiesto(ByVal sTicket As String) As DataTable
        Dim dt As New DataTable
        Dim sql As String = "select m.CodViaje as CodVuelo,DesTramo=lo.NomLugar+'-'+ld.NomLugar,m.CodReserva,m.CodAnalisis,isnull(m.CodComunidad,'       ') as CodComunidad,m.NroDocumentoId,NomPasajero=m.AppPasajero+' '+m.ApmPasajero+', '+m.NomPasajero,m.CodCentroCosto,m.PctCentroCosto,m.CodCentroCosto2,tp.NomTipoPasajero,isnull(m.MonPasaje,0) as MonPasaje,ru.PrePasaje,FlgNoEditar = CAST(isnull(m.FlgNoEditar, 0) AS BIT),m.FlgChecking from d_manifiesto m " &
                            "inner join dbo.V_Reserva r on m.CodReserva=r.CodReserva " &
                            "inner join dbo.M_TipoPasajero tp on r.TipPasajero=tp.CodTipoPasajero " &
                            "inner join dbo.C_Manifiesto c on m.CodViaje=c.CodViaje " &
                            "inner join dbo.m_Rutas ru on c.CodLugarOrigen=ru.CodOrigen and c.CodLugarDestino=ru.CodDestino " &
                            "inner join dbo.m_lugar lo on 'L'+c.CodlugarOrigen=lo.TipLugar+lo.CodLugar " &
                            "inner join dbo.m_lugar ld on 'L'+c.CodlugarDestino=lo.TipLugar+ld.CodLugar " &
                            "where c.NroTicket='" & sTicket & "' order by m.codViaje,m.NroItem"
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Try
                cn.Open()
                Using da As New SqlDataAdapter(sql, cn)
                    da.Fill(dt)
                End Using
                cn.Close()
                Return dt
            Catch myException As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (CargarDetalleManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function EliminarProvision(ByVal sTicket As String) As Boolean
        Const sInstruccion As String = " Update dbo.C_Manifiesto Set NroTicket = @NroTicket, FlgProducto = @lProducto, EstadoManifiesto = @Estado WHERE Nroticket = @NroTicketB"
        Using cn As New SqlConnection(MiConexion.GetCnx)
            cn.Open()
            Dim oTran As SqlTransaction = cn.BeginTransaction
            Try
                ' primero quito los numeros de ticket a los vuelos que estan consolidados
                Dim mySqlCommand As SqlCommand = New SqlCommand(sInstruccion, cn) With {.Transaction = oTran}
                mySqlCommand.Parameters.AddWithValue("@NroTicketB", sTicket)
                mySqlCommand.Parameters.AddWithValue("@NroTicket", DBNull.Value)
                mySqlCommand.Parameters.AddWithValue("@lProducto", False)
                mySqlCommand.Parameters.AddWithValue("@Estado", "2")
                mySqlCommand.ExecuteNonQuery()
                oTran.Commit()
                cn.Close()
                ' ahora elimino el ticket de provision
                cmd.Connection = cn
                cmd.CommandType = CommandType.StoredProcedure
                cmd.CommandText = "pr_EliminarProvision"
                cmd.Parameters.Add(New SqlParameter("@psTicket", SqlDbType.Char))
                cmd.Parameters("@psTicket").Value = sTicket
                cn.Open()
                cmd.ExecuteNonQuery()
                cn.Close()
                Return True
            Catch myException As Exception
                oTran.Rollback()
                cn.Close()
                MsgBox("ConsolidadoADO (ActualizarProvisionManifiesto) --> " & myException.Message, MsgBoxStyle.Critical)
                Return False
            End Try
        End Using
    End Function

    Public Function BuscarCorrelativo(ByVal sAnoMes As String, sCodAsn As String) As String
        Dim snroasn As String = "0001"
        Dim sql As String = String.Format("select anomes,codasn,nroasn+1 as nroasn from dbo.correlativo where anomes='{0}' AND codasn='{1}'", sAnoMes, sCodAsn)
        Using cn As New SqlConnection(ConexionADO.GetCnxContabilidad)
            Try
                cn.Open()
                Using cmd As New SqlCommand(sql, cn)
                    Dim da As SqlDataAdapter = New SqlDataAdapter(cmd)
                    Using dt As DataTable = New DataTable("tasn")
                        da.Fill(dt)
                        Dim drCurrent As DataRow
                        For Each drCurrent In dt.Rows
                            snroasn = drCurrent("nroasn")
                            snroasn = Strings.Right("000" & snroasn.Trim, 4)
                        Next
                    End Using
                End Using
                cn.Close()
                Return snroasn
            Catch ex As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (Buscar_Correlativo) --> " & ex.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function GrabarCorrelativo(ByVal sTicket As String, sAnomes As String, sCodasn As String, sNroAsn As String) As Boolean
        Try
            ' grabo el correlativo en la contabilidad
            Using cn As New SqlConnection(ConexionADO.GetCnxContabilidad)
                cn.Open()
                Dim oTran As SqlTransaction = cn.BeginTransaction
                Dim nnroasn As Integer = sNroAsn
                Dim sInstruccion As String = " Update dbo.correlativo Set nroasn = @nroAsiento WHERE anomes = @nroPeriodo AND codasn = @codAsiento"
                If nnroasn = 1 Then
                    sInstruccion = " Insert Into dbo.correlativo (anomes, codasn, nroasn) Values (@nroPeriodo, @codAsiento, @nroAsiento)"
                End If
                Dim mySqlCommand As SqlCommand = New SqlCommand(sInstruccion, cn) With {.Transaction = oTran}
                mySqlCommand.Parameters.AddWithValue("@nroPeriodo", sAnomes)
                mySqlCommand.Parameters.AddWithValue("@codAsiento", sCodasn)
                mySqlCommand.Parameters.AddWithValue("@nroAsiento", nnroasn)
                mySqlCommand.ExecuteNonQuery()
                oTran.Commit()
                cn.Close()
            End Using
            ' grabo el correlativo en la provision de pasajeros
            Using cn2 As New SqlConnection(MiConexion.GetCnx)
                cn2.Open()
                Dim oTran2 As SqlTransaction = cn2.BeginTransaction
                Const sInstruccion2 As String = " Update dbo.V_Provision Set nroasn = @nroAsiento WHERE nroTicket = @nTicket"
                Dim mySqlCommand2 As SqlCommand = New SqlCommand(sInstruccion2, cn2) With {.Transaction = oTran2}
                mySqlCommand2.Parameters.AddWithValue("@nTicket", sTicket)
                mySqlCommand2.Parameters.AddWithValue("@nroAsiento", sNroAsn)
                mySqlCommand2.ExecuteNonQuery()
                oTran2.Commit()
                cn2.Close()
            End Using
            Return True
        Catch ex As Exception
            MsgBox("ConsolidadoADO (GrabarCorrelativo) --> " & ex.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
    End Function

    Public Function GenerarAsiento(ByVal sTicket As String, sAnoMes As String, sCodAsn As String, sNroAsn As String) As String
        Try
            ' si es nuevo se busca el correlativo
            If sNroAsn.Trim = "" Then
                sNroAsn = BuscarCorrelativo(sAnoMes, sCodAsn)
            End If
            ' si se graba bien el correlativo y provision
            If GrabarCorrelativo(sTicket, sAnoMes, sCodAsn, sNroAsn) Then
                ' genero el asiento: traemos la provision, cabecera manifiesto y detalle manifiesto
                Dim sql As String = "SELECT vp.nroTicket,vp.nroPeriodo,vp.codAsiento,vp.nroAsiento,vp.tipDocumento,vp.nroDocumento,vp.fchIngreso,vp.fchEmision,vp.monTipoCambio,vp.codMoneda,vp.monTotal,vp.nroConPago," &
                                    "vp.flgDetraccion,et.nomEmpresa,et.codAnalisis,cm.codviaje,left(lo.nomlugar,3) as nomlugarorigen,left(ld.nomlugar,3) as nomlugardestino,cm.flgproducto,dm.codCentroCosto,dm.pctCentroCosto," &
                                    "tp.codgasto,tp.coduga,dm.codCentroCosto2,dm.codAnalisis,dm.codComunidad,dm.appPasajero+' '+dm.apmPasajero+', '+dm.nomPasajero as nomPasajero,dm.monPasaje FROM dbo.V_Provision vp " &
                                    "INNER JOIN dbo.m_empresatransporte et ON vp.codempresa=et.codempresa " &
                                    "INNER JOIN dbo.c_manifiesto cm ON vp.nroticket=cm.nroticket " &
                                    "INNER JOIN dbo.d_manifiesto dm ON cm.codviaje=dm.codviaje " &
                                    "INNER JOIN dbo.v_reserva re ON dm.codreserva=re.codreserva " &
                                    "INNER JOIN m_tipopasajero tp ON re.tipPasajero=tp.codTipoPasajero " &
                                    "INNER JOIN dbo.m_lugar lo ON cm.codlugarorigen=lo.codlugar " &
                                    "INNER JOIN dbo.m_lugar ld ON cm.codlugardestino=ld.codlugar " &
                                    "WHERE (vp.nroticket='" & sTicket & "')"
                Dim dt As New DataTable
                Using cn As New SqlConnection(MiConexion.GetCnx)
                    cn.Open()
                    Using da As New SqlDataAdapter(sql, cn)
                        da.Fill(dt)
                    End Using
                    cn.Close()
                    Dim AsntBE As New V_AsientoBE
                    Dim nregistro = 1
                    'recorro el dt
                    For Each fila As DataRow In dt.Rows
                        Dim sreflin As String = fila("nomPasajero")
                        AsntBE.anomes = fila("nroPeriodo")
                        AsntBE.codasn = fila("codAsiento")
                        AsntBE.nroasn = fila("nroAsiento")
                        AsntBE.registro = nregistro.ToString
                        AsntBE.codcta = fila("codCentroCosto")
                        AsntBE.codgas = fila("codgasto")
                        AsntBE.coduga = fila("coduga")
                        AsntBE.codanl = fila("codAnalisis")
                        AsntBE.codaux = fila("codComunidad").ToString
                        AsntBE.fchemi = fila("fchEmision").ToString
                        AsntBE.codtdc = fila("tipDocumento")
                        AsntBE.nrodoc = fila("nroDocumento")
                        AsntBE.reflin = sreflin
                        AsntBE.diaven = fila("nroConPago")
                        AsntBE.Debmna = fila("monPasaje")
                        AsntBE.Habmna = 0
                        AsntBE.Debmex = 0
                        AsntBE.Habmex = 0
                        AsntBE.Montca = fila("monTipoCambio")
                        AsntBE.fchasn = fila("fchIngreso").ToString
                        AsntBE.Creusr = fila("").ToString
                        AsntBE.Crefec = Date.Today
                        AsntBE.Desglo = "PROVISION POR TRASLADO AEREO"
                        ' Grabo el registro en la tabla de transferencia
                        GrabarAsiento(AsntBE)
                        nregistro = nregistro + 1
                    Next
                    Return True
                End Using
            End If
        Catch myException As Exception
            MsgBox("ConsolidadoADO (Listar Consolidado) --> " & myException.Message, MsgBoxStyle.Critical)
            Return Nothing
        End Try
        Return String.Format("{0}-{1}", sCodAsn, sNroAsn)
    End Function

    Public Function GrabarAsiento(ByVal vAsn As V_AsientoBE) As Boolean
        ' grabo la linea del asiento
        Using cn As New SqlConnection(ConexionADO.GetCnxContabilidad)
            Try
                cn.Open()
                Dim oTran As SqlTransaction = cn.BeginTransaction
                Dim sInstruccion As String = " Insert Into dbo.V_Asiento (anomes,codasn,nroasn,registro,codcta,codgas,coduga,codanl,codaux,fchemi,codtdc,nrodoc,reflin,diaven,debmna,habmna,debmex,habmex,montca,fchasn,creusr,crefec,desglo) Values " &
                                             "(@anomes,@codasn,@nroasn,@registro,@codcta,@codgas,@coduga,@codanl,@codaux,@fchemi,@codtdc,@nrodoc,@reflin,@diaven,@debmna,@habmna,@debmex,@habmex,@montca,@fchasn,@creusr,@crefec,@desglo)"
                Dim mySqlCommand As SqlCommand = New SqlCommand(sInstruccion, cn) With {.Transaction = oTran}
                mySqlCommand.Parameters.AddWithValue("@anomes", vAsn.anomes)
                mySqlCommand.Parameters.AddWithValue("@codasn", vAsn.codasn)
                mySqlCommand.Parameters.AddWithValue("@nroasn", vAsn.nroasn)
                mySqlCommand.Parameters.AddWithValue("@registro", vAsn.registro)
                mySqlCommand.Parameters.AddWithValue("@codcta", vAsn.codcta)
                mySqlCommand.Parameters.AddWithValue("@codgas", vAsn.codgas)
                mySqlCommand.Parameters.AddWithValue("@coduga", vAsn.coduga)
                mySqlCommand.Parameters.AddWithValue("@codanl", vAsn.codanl)
                mySqlCommand.Parameters.AddWithValue("@codaux", vAsn.codaux)
                mySqlCommand.Parameters.AddWithValue("@fchemi", vAsn.fchemi)
                mySqlCommand.Parameters.AddWithValue("@codtdc", vAsn.codtdc)
                mySqlCommand.Parameters.AddWithValue("@nrodoc", vAsn.nrodoc)
                mySqlCommand.Parameters.AddWithValue("@reflin", vAsn.reflin)
                mySqlCommand.Parameters.AddWithValue("@diaven", vAsn.diaven)
                mySqlCommand.Parameters.AddWithValue("@debmna", vAsn.Debmna)
                mySqlCommand.Parameters.AddWithValue("@habmna", vAsn.Habmna)
                mySqlCommand.Parameters.AddWithValue("@debmex", vAsn.Debmex)
                mySqlCommand.Parameters.AddWithValue("@habmex", vAsn.Habmex)
                mySqlCommand.Parameters.AddWithValue("@montca", vAsn.Montca)
                mySqlCommand.Parameters.AddWithValue("@fchasn", vAsn.fchasn)
                mySqlCommand.Parameters.AddWithValue("@creusr", vAsn.Creusr)
                mySqlCommand.Parameters.AddWithValue("@crefec", vAsn.Crefec)
                mySqlCommand.Parameters.AddWithValue("@desglo", vAsn.Desglo)
                mySqlCommand.ExecuteNonQuery()
                oTran.Commit()
                cn.Close()
                Return True
            Catch ex As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (GrabarAsiento) --> " & ex.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function ListarConsolidado(ByVal sFiltro As String) As DataTable
        Dim sql As String = "SELECT p.NroPeriodo, p.CodAsiento+p.NroAsiento as Asiento, p.NroTicket, p.TipDocumento, p.NroDocumento, p.FchIngreso, p.FchEmision, p.FchVuelo," &
                            "p.NroConPago, p.MonTipoCambio, p.CodMoneda, m.NomMoneda, p.MonTotal, p.CodEmpresa, e.CodAnalisis, e.NomEmpresa as DesAnalisis FROM dbo.V_Provision p " &
                            "INNER JOIN M_EmpresaTransporte e on p.codEmpresa=e.CodEmpresa INNER JOIN M_Moneda m ON p.CodMoneda=m.CodMoneda"
        Dim scriterio As String = IIf(sFiltro.Trim() = "", "", " Where " & Trim(sFiltro))
        Dim dt As New DataTable
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Try
                cn.Open()
                Using da As New SqlDataAdapter(sql & scriterio, cn)
                    da.Fill(dt)
                End Using
                cn.Close()
                Return dt
            Catch myException As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (Listar Consolidado) --> " & myException.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function ListarTickets(ByVal sFiltro As String) As DataTable
        Dim sql As String = "SELECT p.nroPeriodo, p.nroTicket, p.fchIngreso, e.nomEmpresa, p.CodAsiento, p.NroAsiento, p.CodAsiento+'-'+p.NroAsiento as Asiento, p.nroDocumento, p.nroConPago,p.fchEmision," &
                            "p.monTipoCambio, p.monTotal, p.fchVuelo, CAST(case p.NroAsiento when '    ' then 0 when NULL then 0 else 1 end AS BIT) AS flgSeleccion " &
                            "FROM dbo.V_Provision p INNER JOIN M_EmpresaTransporte e on p.codEmpresa=e.CodEmpresa INNER JOIN M_Moneda m ON p.CodMoneda=m.CodMoneda"
        Dim scriterio As String = IIf(sFiltro.Trim() = "", "", " Where " & Trim(sFiltro))
        Dim dt As New DataTable
        Using cn As New SqlConnection(MiConexion.GetCnx)
            Try
                cn.Open()
                Using da As New SqlDataAdapter(sql & scriterio, cn)
                    da.Fill(dt)
                End Using
                cn.Close()
                Return dt
            Catch myException As Exception
                cn.Close()
                MsgBox("ConsolidadoADO (listarTickets) --> " & myException.Message, MsgBoxStyle.Critical)
                Return Nothing
            End Try
        End Using
    End Function

    Public Function TraerProvision(ByVal snroticket As String) As ConsolidadoBE
        Dim _rBE As New ConsolidadoBE
        Try
            Dim dtr As SqlDataReader
            cmd.Connection = cnx
            cmd.CommandType = CommandType.StoredProcedure
            cmd.CommandText = "pr_TraerProvision"
            cmd.Parameters.Add(New SqlParameter("@pNroTicket", SqlDbType.Char))
            cmd.Parameters("@pNroTicket").Value = snroticket
            cnx.Open()
            dtr = cmd.ExecuteReader
            If dtr.HasRows = True Then
                dtr.Read()
                With _rBE
                    .NroTicket = dtr("NroTicket")
                    .NroPeriodo = dtr("NroPeriodo")
                    .CodAsiento = dtr("CodAsiento")
                    .NroAsiento = dtr("NroAsiento")
                    .TipDocumento = dtr("TipDocumento")
                    .NroDocumento = dtr("NroDocumento")
                    If IsDBNull(dtr("FchIngreso")) Then
                        .FchIngreso = Date.Now
                    Else
                        .FchIngreso = dtr("FchIngreso")
                    End If
                    If IsDBNull(dtr("FchEmision")) Then
                        .FchEmision = Date.Now
                    Else
                        .FchEmision = dtr("FchEmision")
                    End If
                    If IsDBNull(dtr("FchVuelo")) Then
                        .FchVuelo = Date.Now
                    Else
                        .FchVuelo = dtr("FchVuelo")
                    End If
                    .MonTipoCambio = dtr("MonTipoCambio")
                    .CodMoneda = dtr("CodMoneda")
                    .MonTotal = dtr("MonTotal")
                    .CodEmpresa = dtr("CodEmpresa")
                    .NroConPago = dtr("NroConPago")
                    .FlgDetraccion = dtr("FlgDetraccion")
                End With
            End If
            cmd.Parameters.Clear()
        Catch x As SqlException
            MsgBox("ConsolidadoADO (TraerProvision) --> " & x.Message, MsgBoxStyle.Critical)
        Finally
            If cnx.State = ConnectionState.Open Then
                cnx.Close()
            End If
        End Try
        Return _rBE
    End Function
End Class